from sqlalchemy import Column, text
from sqlalchemy.dialects.postgresql import \
        SMALLINT, INTEGER, TEXT, DATE, TIMESTAMP, UUID, JSON

from . import Base


class User(Base):
    """用户基本资料/users
    
    用户编号      userID        UUID
    头像          avatar        URL
    昵称          nickname      Text
    性别          gender        Text
    出生日期      birthday      Date
    地区          district      JSON
    擅长位置      prefPosition  JSON
    身高          height        Integer
    体重          weight        Integer
    惯用脚        prefFoot      Text
    个人宣言      declaration   Text
    真实姓名      realname      Text
    身份证号      IDCardNumber  Text
    身份证正反照  IDCardPhoto   JSON
    手机号码      mobile        Text
    邮箱          email         Text
    密码          password      Text
    注册时间      createdTime   DateTime
    状态          status        Integer
    
    地区格式:
        {"Province":"北京市","Prefectural":"海淀区","County":"中关村"}
    擅长位置:
        ["1", "2"]
    
    Auto Generated Code.
    """
    __tablename__ = 'users'

    userid = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    avatar = Column(TEXT)
    nickname = Column(TEXT, unique=True)
    gender = Column(TEXT)
    birthday = Column(DATE)
    district = Column(JSON)
    prefposition = Column(JSON)
    height = Column(INTEGER)
    weight = Column(INTEGER)
    preffoot = Column(TEXT)
    declaration = Column(TEXT)
    realname = Column(TEXT)
    idcardnumber = Column(TEXT)
    idcardphoto = Column(JSON)
    mobile = Column(TEXT)
    email = Column(TEXT)
    password = Column(TEXT, nullable=False)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))


class Club(Base):
    """俱乐部基本资料/clubs
    
    俱乐部编号  clubID       UUID
    名称        name         Text
    队徽        logo         URL
    背景图      background   URL
    创立城市    district     JSON
    创立日期    foundDate    Date
    口号        slogan       Text
    简介        intro        Text
    创始人      founder      UUID
    创建时间    createdTime  DateTime
    状态        status       Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'clubs'

    clubid = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    name = Column(TEXT, unique=True, nullable=False)
    logo = Column(TEXT)
    background = Column(TEXT)
    district = Column(JSON)
    founddate = Column(DATE, 
            server_default=text("current_date"))
    slogan = Column(TEXT)
    intro = Column(TEXT)
    founder = Column(UUID(as_uuid=True), nullable=False)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))


class Honor(Base):
    """俱乐部荣誉/honors
    
    ID          id           UUID
    名称        honor        Text
    描述        intro        Text
    获奖日期    dateCreated  Date
    俱乐部编号  clubID       UUID
    
    Auto Generated Code.
    """
    __tablename__ = 'honors'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    honor = Column(TEXT, nullable=False)
    intro = Column(TEXT)
    datecreated = Column(DATE)
    clubid = Column(UUID(as_uuid=True))


class Member(Base):
    """俱乐部成员/members
    
    俱乐部编号  clubID       UUID
    用户编号    userID       UUID
    角色        role         Text
    加入时间    createdTime  DateTime
    状态        status       Integer
    
    角色/role
        创始人/founder
        管理员/manager
        成员/member
    
    Auto Generated Code.
    """
    __tablename__ = 'members'

    clubid = Column(UUID(as_uuid=True), primary_key=True)
    userid = Column(UUID(as_uuid=True), primary_key=True)
    role = Column(TEXT)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))


class Follower(Base):
    """关注/followers
    
    所有者    owner        UUID
    追随者    follower     UUID
    类型      type         Integer
    创建时间  createdTime  DateTime
    
    Auto Generated Code.
    """
    __tablename__ = 'followers'

    owner = Column(UUID(as_uuid=True), primary_key=True)
    follower = Column(UUID(as_uuid=True), primary_key=True)
    type = Column(SMALLINT)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))


class League(Base):
    """联赛基本数据/leagues
    
    联赛编号   leagueID     UUID
    名称       name         Text
    地区       district     JSON
    简介       intro        Text
    类型       type         Integer
    创建时间   createdTime  DateTime
    状态       status       Integer
    
    类型/type
        联赛
        杯赛
    
    Auto Generated Code.
    """
    __tablename__ = 'leagues'

    leagueid = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    name = Column(TEXT, unique=True, nullable=False)
    district = Column(JSON)
    intro = Column(TEXT)
    type = Column(INTEGER)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))


class Team(Base):
    """联赛球队/teams
    
    联赛编号    leagueID  UUID
    俱乐部编号  clubID    UUID
    组别        group     Text
    状态        status    Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'teams'

    leagueid = Column(UUID(as_uuid=True), primary_key=True)
    clubid = Column(UUID(as_uuid=True), primary_key=True)
    group = Column(TEXT)
    status = Column(INTEGER, 
            server_default=text("0"))


class PlayerList(Base):
    """名单/playerlists
    
    联赛编号    leagueID       UUID
    俱乐部编号  clubID         UUID
    用户编号    userID         UUID
    真实姓名    realname       Text
    出生日期    birthday       Date
    球衣背号    clubNumber     Integer
    位置        fieldPosition  Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'playerlists'

    leagueid = Column(UUID(as_uuid=True), primary_key=True)
    clubid = Column(UUID(as_uuid=True), primary_key=True)
    userid = Column(UUID(as_uuid=True), primary_key=True)
    realname = Column(TEXT)
    birthday = Column(DATE)
    clubnumber = Column(INTEGER)
    fieldposition = Column(INTEGER)


class Match(Base):
    """球赛基本数据/matches
    
    球赛编号   matchID      UUID
    联赛编号   leagueID     UUID
    序号       session      Integer
    开始时间   startedTime  DateTime
    报名截止   deadline     DateTime
    主队       homeTeam     UUID
    客队       awayTeam     UUID
    比分       score        JSON
    MVP        MVP          UUID
    场地       site         Text
    球衣要求   shirt        Text
    注意事项   notes        Text
    创建时间   createdTime  DateTime
    类型       type         Integer
    状态       status       Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'matches'

    matchid = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    leagueid = Column(UUID(as_uuid=True))
    session = Column(INTEGER)
    startedtime = Column(TIMESTAMP(timezone=True), nullable=False)
    deadline = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    hometeam = Column(UUID(as_uuid=True), nullable=False)
    awayteam = Column(UUID(as_uuid=True), nullable=False)
    score = Column(JSON)
    mvp = Column(UUID(as_uuid=True))
    site = Column(TEXT)
    shirt = Column(TEXT)
    notes = Column(TEXT)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    type = Column(INTEGER)
    status = Column(INTEGER, 
            server_default=text("0"))


class LineUp(Base):
    """出场阵容/lineups
    
    球赛编号    matchID        UUID
    俱乐部编号  clubID         UUID
    用户编号    userID         UUID
    球衣背号    clubNumber     Integer
    位置        fieldPosition  Integer
    报名状态    applied        Integer
    是否到场    present        Integer
    是否首发    starting       Integer
    是否上场    field          Integer
    报名时间    createdTime    DateTime
    
    报名状态/applied
        已报名
        已请假
    
    Auto Generated Code.
    """
    __tablename__ = 'lineups'

    matchid = Column(UUID(as_uuid=True), primary_key=True)
    clubid = Column(UUID(as_uuid=True), primary_key=True)
    userid = Column(UUID(as_uuid=True), primary_key=True)
    clubnumber = Column(INTEGER)
    fieldposition = Column(INTEGER)
    applied = Column(INTEGER)
    present = Column(INTEGER)
    starting = Column(INTEGER)
    field = Column(INTEGER)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))


class Event(Base):
    """球赛事件/events
    
    ID          id            UUID
    球赛编号    matchID       UUID
    俱乐部编号  clubID        UUID
    用户编号    userID        UUID
    事件        event         Integer
    时间        occurredTime  DateTime
    
    进球
        进球
        乌龙球
    红黄牌
        红牌
        黄牌
    其他
        警告
    
    Auto Generated Code.
    """
    __tablename__ = 'events'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    matchid = Column(UUID(as_uuid=True), nullable=False)
    clubid = Column(UUID(as_uuid=True), nullable=False)
    userid = Column(UUID(as_uuid=True), nullable=False)
    event = Column(INTEGER, nullable=False)
    occurredtime = Column(TIMESTAMP(timezone=True), nullable=False)


class SpiritBorad(Base):
    """球风记分牌/spiritborads
    
    ID          id           UUID
    联赛编号    leagueID     UUID
    俱乐部编号  clubID       UUID
    记分        point        Integer
    类型        type         Integer
    创建时间    createdTime  DateTime
    
    Auto Generated Code.
    """
    __tablename__ = 'spiritborads'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    leagueid = Column(UUID(as_uuid=True), nullable=False)
    clubid = Column(UUID(as_uuid=True), nullable=False)
    point = Column(INTEGER, nullable=False)
    type = Column(INTEGER, nullable=False)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))


class Table(Base):
    """积分榜/tables
    
    联赛编号    leagueID     UUID
    俱乐部编号  clubID       UUID
    场数        played       Integer
    积分        points       Integer
    胜          wins         Integer
    平          draws        Integer
    负          losses       Integer
    进球        goalScored   Integer
    失球        goalAgainst  Integer
    更新时间    lastUpdated  DateTime
    
    Auto Generated Code.
    """
    __tablename__ = 'tables'

    leagueid = Column(UUID(as_uuid=True), primary_key=True)
    clubid = Column(UUID(as_uuid=True), primary_key=True)
    played = Column(INTEGER)
    points = Column(INTEGER)
    wins = Column(INTEGER)
    draws = Column(INTEGER)
    losses = Column(INTEGER)
    goalscored = Column(INTEGER)
    goalagainst = Column(INTEGER)
    lastupdated = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))


class Spirit(Base):
    """球风榜/spirits
    
    联赛编号    leagueID  UUID
    俱乐部编号  clubID    UUID
    积分        points    Integer
    红牌        proj01    Integer
    黄牌        proj02    Integer
    警告        proj03    Integer
    列队        proj04    Integer
    队服        proj05    Integer
    鼓掌        proj06    Integer
    复盘        proj07    Integer
    海报        proj08    Integer
    投票        proj09    Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'spirits'

    leagueid = Column(UUID(as_uuid=True), primary_key=True)
    clubid = Column(UUID(as_uuid=True), primary_key=True)
    points = Column(INTEGER)
    proj01 = Column(INTEGER)
    proj02 = Column(INTEGER)
    proj03 = Column(INTEGER)
    proj04 = Column(INTEGER)
    proj05 = Column(INTEGER)
    proj06 = Column(INTEGER)
    proj07 = Column(INTEGER)
    proj08 = Column(INTEGER)
    proj09 = Column(INTEGER)


class Rank(Base):
    """排行榜/ranks
    
    ID          id           UUID
    联赛编号    leagueID     UUID
    俱乐部编号  clubID       UUID
    用户编号    userID       UUID
    分值        score        Integer
    附加数据    ext          Text
    类型        type         Integer
    更新时间    lastUpdated  DateTime
    
    类型/type
        射手
        助攻
        红牌
        黄牌
    
    Auto Generated Code.
    """
    __tablename__ = 'ranks'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    leagueid = Column(UUID(as_uuid=True))
    clubid = Column(UUID(as_uuid=True))
    userid = Column(UUID(as_uuid=True))
    score = Column(INTEGER)
    ext = Column(TEXT)
    type = Column(INTEGER)
    lastupdated = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))


class District(Base):
    """行政区划/district
    
    地区编号      id           UUID
    上级地区编号  pid          UUID
    地区名称      district     Text
    行政区划代码  code         Text
    状态          status       Integer
    
    Auto Generated Code.
    """
    __tablename__ = 'districts'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    pid = Column(UUID(as_uuid=True))
    district = Column(TEXT)
    code = Column(TEXT)
    status = Column(INTEGER, 
            server_default=text("0"))

class Video(Base):
    """视频表/video
    生成id              id           UUID
    访问地址            url          TEXT
    作者                author       TEXT
    时长                duration     Integer
    大小                size         Integer
    类型:外链-0,本地-1  type         Integer
    地址                path         TEXT
    上传时间            createdTime  DateTime
    状态:0-无效 1-有效  status       Integer
    """
    __tablename__ = 'video'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    url = Column(TEXT)
    author = Column(TEXT)
    duration = Column(INTEGER, 
            server_default=text("0"))
    size = Column(INTEGER, 
            server_default=text("0"))
    type = Column(INTEGER) 
    path = Column(TEXT)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))

class Image(Base):

    """图片基础表/image
    图片id    id           UUID
    访问地址  URL          UUID
    作者      author       TEXT
    宽度      width        Integer 
    长度      height       Integer 
    大小      size         Integer 
    存放位置  path         TEXT
    创建日期  createdtime  DateTime 
    状态      status       Integer
    """

    __tablename__ = 'image'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    url = Column(TEXT)
    author = Column(TEXT)

    width = Column(INTEGER, 
            server_default=text("0"))
    height = Column(INTEGER, 
            server_default=text("0"))
    size = Column(INTEGER, 
            server_default=text("0"))
    path = Column(TEXT)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))
    status = Column(INTEGER, 
            server_default=text("0"))

class VideoSet(Base):

    """视频集锦表/videoset
    视频id    id         UUID
    俱乐部id  clubID     UUID    
    比赛id    matchID    UUID    
    视频id    videoID    UUID    
    类型      type       Integer    
    状态      status     Integer    
    """
    __tablename__ = 'videoset'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    clubid = Column(UUID(as_uuid=True), nullable=False)
    matchid = Column(UUID(as_uuid=True), nullable=False)
    videoid = Column(UUID(as_uuid=True), nullable=False)
    type = Column(INTEGER, 
            server_default=text("0"))
    status = Column(INTEGER, 
            server_default=text("0"))

class ImageSet(Base):

    """视频集锦表/videoset
    视频id    id         UUID
    俱乐部id  clubID     UUID    
    比赛id    matchID    UUID    
    图片id    imageID    UUID    
    类型      type       Integer    
    状态      status     Integer    
    """
    __tablename__ = 'imageset'

    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    clubid = Column(UUID(as_uuid=True), nullable=False)
    matchid = Column(UUID(as_uuid=True), nullable=False)
    imageid = Column(UUID(as_uuid=True), nullable=False)
    type = Column(INTEGER, 
            server_default=text("0"))
    status = Column(INTEGER, 
            server_default=text("0"))


class Msg(Base):
    """消息/msg
    消息id    id         UUID
    内容      content    TEXT
    类型      type       Integer
    状态      status     Integer
    """
    __tablename__ = 'msg'
    
    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))
    content = Column(TEXT)
    type = Column(INTEGER, 
            server_default=text("0"))
    status = Column(INTEGER, 
            server_default=text("0"))

class MsgQueue(Base):
    """消息队列/msgqueue
    消息队列id    id           UUID
    发送者        sender       UUID
    接收者        receiver     UUID
    创建日期      createdtime  DateTime 
    内容id        contentID    UUID
    是否阅读      isRead       Integer
    阅读日期      readDate     DateTime
    状态          status       Integer
    """

    __tablename__ = 'msgqueue'
    id = Column(UUID(as_uuid=True), primary_key=True, 
            server_default=text("uuid_generate_v1mc()"))

    sender = Column(UUID(as_uuid=True), nullable=False)
    receiver = Column(UUID(as_uuid=True), nullable=False)
    createdtime = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))

    contentid = Column(UUID(as_uuid=True), nullable=False)

    isread = Column(INTEGER, 
            server_default=text("0"))

    
    readdate = Column(TIMESTAMP(timezone=True), 
            server_default=text("current_timestamp"))

    status = Column(INTEGER, 
            server_default=text("0"))

